﻿

-- B1: Tính hết tất cả các dòng tiền ghi có vào tài khoản của khách hàng ( cả current account và term deposit)
		-- FT vs TT map vào acctno vào DPTB_MASTER (segment = SME)

   --- Xử lý bảng FT_HIST


select BUSINESS_DATE,id,TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG
  into FT_KSSV_201511	
	from ad_hoc.dbo.FT_HIST_201511 
		where (
				(DEBIT_ACCOUNT in (	select ACCTNO  from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme' )) 
							or 
				(CREDIT_ACCOUNT in (	select ACCTNO  from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme' ))
			   )

--select distinct credit_ccy from FT_KSSV_201511 where  cif_debit is null and cif_credit is null
 -- update cif_debit và cif_credit

 alter table FT_KSSV_201511
 add cif_debit varchar(20),cif_credit varchar(20),check_cif	varchar(4), amount float, amount_qd float, tygia float, CIF VARCHAR(30)

 -- update cif_debit
update FT_KSSV_201511
set cif_debit=b.cif
from FT_KSSV_201511 a, ( select *  
							from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme') b
where a.DEBIT_ACCOUNT= b.acctno


 -- update cif_debit
update FT_KSSV_201511
set cif_credit=b.cif
from FT_KSSV_201511 a, ( select *  
							from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme') b
where a.CREDIT_ACCOUNT= b.acctno

  -- update check_cif	 ( SELECT * FROM FT_KSSV_201511 WHERE CIF IS NULL )

update FT_KSSV_201511
set check_cif = case when cif_debit = cif_credit then '0' else '1' end

-- UPDATE CIF
   update FT_KSSV_201511
   SET CIF = cif_credit

   update FT_KSSV_201511
   SET CIF = cif_debit
   WHERE CIF IS NULL

 -- update amount


 update FT_KSSV_201511
 set amount = case	when CREDIT_amount not like '%ý%' then CREDIT_AMOUNT else DEBIT_AMOUNT end

 -- update tỷ giá

 update FT_KSSV_201511
 set tygia = b.mid_rate
 from FT_KSSV_201511 a, (select * from server16.vpb_whr2.dbo.focurr_save where  save_date = '20151130') b
 where a.CREDIT_CCY =b.code 

  --- update amount_qd

  update FT_KSSV_201511
  set amount_qd = amount*tygia



select BUSINESS_DATE,id,TRANSACTION_CODE as TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG from ad_hoc.dbo.TT_HIST_201511 -----cả tháng, server 16 


 
   --- Xử lý bảng TT_HIST


select BUSINESS_DATE,id,TRANSACTION_CODE as TRANSACTION_TYPE,DEBIT_ACCOUNT,DEBIT_AMOUNT,CREDIT_ACCOUNT,CREDIT_AMOUNT,CREDIT_CCY,CREDIT_VALUE_DATE,CREDIT_CATEG
,DEBIT_CATEG 
  into TT_KSSV_201511	
	from ad_hoc.dbo.TT_HIST_201511
		where (
				(DEBIT_ACCOUNT in (	select ACCTNO  from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme' )) 
							or 
				(CREDIT_ACCOUNT in (	select ACCTNO  from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme' ))
			   )

--select distinct credit_ccy from FT_KSSV_201511 where  cif_debit is null and cif_credit is null
 -- update cif_debit và cif_credit

 alter table TT_KSSV_201511
 add cif_debit varchar(20),cif_credit varchar(20),check_cif	varchar(4), amount float, amount_qd float, tygia float,CIF VARCHAR(30)


 -- update cif_debit
update TT_KSSV_201511
set cif_debit=b.cif
from TT_KSSV_201511 a, ( select *  
							from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme') b
where a.DEBIT_ACCOUNT= b.acctno


 -- update cif_debit
update TT_KSSV_201511
set cif_credit=b.cif
from TT_KSSV_201511 a, ( select *  
							from server74.bicdata.dbo.dptb_master
								where bi_segment = 'sme') b
where a.CREDIT_ACCOUNT= b.acctno

  -- update check_cif

update TT_KSSV_201511
set check_cif = case when cif_debit = cif_credit then '0' else '1' end

 -- UPDATE CIF
   update TT_KSSV_201511
   SET CIF = cif_credit

   update TT_KSSV_201511
   SET CIF = cif_debit
   WHERE CIF IS NULL


 -- update amount
	   
	   -- SELECT * FROM TT_KSSV_201511 WHERE DEBIT_amount like N'%ý%'

 update TT_KSSV_201511
 set amount = b.amount
 from (select case	when isnumeric(CREDIT_amount) = 1 then credit_amount 
					else debit_amount end as amount, id
		from TT_KSSV_201511) B
where  TT_KSSV_201511.id = b.id


 

 -- update tỷ giá

 update TT_KSSV_201511
 set tygia = b.mid_rate
 from TT_KSSV_201511 a, (select * from server16.vpb_whr2.dbo.focurr_save where  save_date = '20151130') b
 where a.CREDIT_CCY =b.code 

  --- update amount_qd

  update TT_KSSV_201511
  set amount_qd = amount*tygia

---- GHÉP 2 BẢNG TT_KSSV_201511 VÀ FT_KSSV_201511 ( LOẠI NHỮNG TRƯỜNG HỢP CÙNG CHUNG 1 CIF KH)
						 --DROP TABLE FT_TT_KSSV_201511	   -- SELECT * FROM  FT_TT_KSSV_201511_1
SELECT * 
INTO FT_TT_KSSV_201511_1
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM TT_KSSV_201511
 WHERE CHECK_CIF = '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_KSSV_201511
 WHERE CHECK_CIF = '1'
) X

------ trường hợp chung cif		 select * from 	 FT_TT_KSSV_201511_0
SELECT * 
INTO FT_TT_KSSV_201511_0
FROM 
 (
 SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF 
 FROM TT_KSSV_201511
 WHERE CHECK_CIF <> '1' 
 UNION ALL
  SELECT BUSINESS_DATE,ID,TRANSACTION_TYPE,DEBIT_ACCOUNT,CREDIT_ACCOUNT,CREDIT_CATEG,CREDIT_VALUE_DATE,CIF_DEBIT,CIF_CREDIT,CHECK_CIF,AMOUNT_QD,TYGIA,CIF  
  FROM FT_KSSV_201511
 WHERE CHECK_CIF <> '1'
) X


------------------ TRẢ NỢ GỐC-LẠI KHÁCH HÀNG	DROP TABLE DONG_TIEN_GHI_CO_201511	 -- select * from FT_TT_KSSV_201511 where 
 
SELECT A.CIF,B.CUS_NAME,B.COMPANY_BOOK,C.BRANCH_NAME_SME,C.ZONE_ID_SME,SUM(A.AMOUNT_QD)/1E9 AS AMOUNT 
INTO DONG_TIEN_GHI_CO_201511 
FROM FT_TT_KSSV_201511_1	A
LEFT JOIN SERVER16.VPB_WHR2.DBO.VPB_CUSTOMER B
ON A.CIF = B.RECID COLLATE DATABASE_DEFAULT
LEFT JOIN CEO_PERFORMANCE.DBO.BRANCH_CODE C
ON B.COMPANY_BOOK=C.BRANCH_ID
WHERE B.COMPANY_BOOK NOT IN (SELECT * FROM CEO_PERFORMANCE.DBO.branchoff)
AND A.CIF NOT IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM CEO_PERFORMANCE.DBO.SME_QUICKWIN_20160204)
GROUP BY A.CIF,B.CUS_NAME,B.COMPANY_BOOK,C.BRANCH_NAME_SME,C.ZONE_ID_SME 

  ALTER TABLE DONG_TIEN_GHI_CO_201511
  ADD TRA_NO FLOAT

										
		 

UPDATE DONG_TIEN_GHI_CO_201511
SET TRA_NO = B.REPAY_AMOUNT
FROM DONG_TIEN_GHI_CO_201511 A,  (SELECT  cif, sum(repay_amount)/1e9 as repay_amount  
							FROM SERVER74.STAGING.DBO.LNTB_LDPD_REPAYED
							WHERE  REPAY_DATE BETWEEN '20151101' AND '20151130'					------- THAY NGÀY TRẢ NỢ
							and CIF IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM FT_TT_KSSV_201511_1)
							AND CIF NOT IN (SELECT CIF COLLATE DATABASE_DEFAULT FROM CEO_PERFORMANCE.DBO.SME_QUICKWIN_20160204)
							--AND CO_CODE NOT IN (SELECT * FROM CEO_PERFORMANCE.DBO.branchoff)
							and segment = 'smes'
							GROUP BY CIF  ) B
WHERE A.CIF=B.CIF COLLATE DATABASE_DEFAULT

  ALTER TABLE DONG_TIEN_GHI_CO_201511
  ADD DOANH_THU_GHI_CO	FLOAT

  UPDATE DONG_TIEN_GHI_CO_201511
  SET DOANH_THU_GHI_CO = ISNULL(AMOUNT,0) - ISNULL(TRA_NO,0) 
 

 SELECT * FROM 	DONG_TIEN_GHI_CO_201511
 WHERE DOANH_THU_GHI_CO <0


